package com.ruoyi.system.util;

import cn.hutool.core.date.DateUtil;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.oss.constant.OssConstant;
import com.ruoyi.common.oss.core.OssClient;
import com.ruoyi.common.oss.enumd.AccessPolicyType;
import com.ruoyi.common.oss.properties.OssProperties;
import com.ruoyi.system.domain.ArchiveLocation;
import com.ruoyi.system.domain.ArchiveResult;
import com.ruoyi.system.enums.ArchiveLocationTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringEscapeUtils;

import java.io.File;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @author lixbin
 * @ClassName ArchiveUtil
 * @datetime 2024年 12月 21日 13:04
 * @version: 1.0
 */
@Slf4j
public class ArchiveUtil {
    public static String getInsertHeadSql(String table, List<String> cells) {
        StringBuffer sb = new StringBuffer("REPLACE INTO ").append(table).append(" (");
        for (String cell : cells) {
            sb.append("`").append(cell).append("`,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")\n VALUES \n");
        return sb.toString();
    }

    public static String getInsertSql(String table, List<String> cells, List<Map<String, Object>> values) {
        StringBuffer sb = new StringBuffer(getInsertHeadSql(table, cells));
        for (Map<String, Object> value : values) {
            sb.append(getDataSqlLine(cells, value));
        }
        return sb.toString();
    }

    public static String getDataSqlLines(List<Map<String, Object>> temp, List<String> cells) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < temp.size(); i++) {
            sb.append(getDataSqlLine(cells, temp.get(i)));
            if (i < temp.size() - 1) {
                sb.append(",\n");
            }
        }
        return sb.toString();
    }

    public static List<String> getDataSqlLineList(List<Map<String, Object>> temp, List<String> cells) {
        List<String> values = new ArrayList<>();
        for (int i = 0; i < temp.size(); i++) {
            StringBuffer sb = new StringBuffer();
            sb.append(getDataSqlLine(cells, temp.get(i)));
            if (i < temp.size() - 1) {
                sb.append(",\n");
            }
            values.add(sb.toString());
        }
        return values;
    }

    public static String getDataSqlLine(List<String> cells, Map<String, Object> data) {
        StringBuffer sb = new StringBuffer("(");
        for (int i = 0; i < cells.size(); i++) {
            String cell = cells.get(i);
            if (data.get(cell) == null) {
                sb.append("NULL");
            } else if (org.apache.commons.lang3.StringUtils.isNumeric(data.get(cell).toString())) {
                sb.append(data.get(cell).toString());
            } else {
                String value = StringEscapeUtils.escapeSql(data.get(cell).toString());
                sb.append("'").append(value).append("'");
            }
            if (i < cells.size() - 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        return sb.toString();
    }

    public static String getSelectHeadSql(String table, List<String> cells) {
        StringBuffer sb = new StringBuffer("SELECT ");
        for (String cell : cells) {
            sb.append("t.").append(cell).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" FROM ").append(table);
        return sb.toString();
    }

    public static String getSelectSql(String table, List<String> cells, String pkCell, String minPk, String maxPk) {
        StringBuffer sb = new StringBuffer(getSelectHeadSql(table, cells));
        sb.append(" t WHERE t.").append(pkCell).append(" > ");
        if (StringUtils.isNumeric(minPk)) {
            sb.append(minPk).append(" AND t.").append(pkCell).append(" <= ").append(maxPk);
        } else {
            sb.append("'").append(minPk).append("' AND t.").append(pkCell).append(" <= '").append(maxPk).append("'");
        }
        sb.append(" ORDER BY t.").append(pkCell).append(" LIMIT ").append(Constants.BATCH_SIZE);
        return sb.toString();
    }

    public static String getSelectSql(String table, List<String> cells, String pkCell, String pk) {
        StringBuffer sb = new StringBuffer(getSelectHeadSql(table, cells));
        sb.append(" t WHERE t.").append(pkCell).append(" = ");
        if (StringUtils.isNumeric(pk)) {
            sb.append(pk);
        } else {
            sb.append("'").append(pk).append("'");
        }
        return sb.toString();
    }

    public static String getInfo(String info) {
        return String.format("%s：%s", DateUtil.formatDateTime(new Date()), info);
    }

    public static int pkCompareTo(Object pk1, Object pk2) {
        if (org.apache.commons.lang3.StringUtils.isNumeric(pk1.toString())) {
            return Long.valueOf(pk1.toString()).compareTo(Long.valueOf(pk2.toString()));
        }
        return pk1.toString().compareTo(pk2.toString());
    }

    public static Connection getDbConnection(ArchiveLocation location) throws SQLException {
        return DriverManager.getConnection(location.getDbUrl(), location.getDbUser(), location.getDbPassword());
    }

    public static List<Map<String, Object>> queryDb(Connection connection, String sql, List<String> cells) throws SQLException {
        log.info(sql);
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            try (ResultSet resultSet = ps.executeQuery()) {
                List<Map<String, Object>> list = new ArrayList<>();
                while (resultSet.next()) {
                    Map<String, Object> temp = new HashMap<>();
                    for (String cell : cells) {
                        temp.put(cell, resultSet.getObject(cell));
                    }
                    list.add(temp);
                }
                return list;
            }
        }
    }

    public static OssClient getS3Client(ArchiveLocation location) {
        OssProperties properties = new OssProperties();
        properties.setIsHttps(location.getS3IsHttps());
        properties.setRegion(location.getS3Region());
        properties.setSecretKey(location.getS3SecretKey());
        properties.setAccessKey(location.getS3AccessKey());
        properties.setEndpoint(location.getS3Endpoint());
        properties.setBucketName(location.getS3BucketName());
        properties.setAccessPolicy(AccessPolicyType.CUSTOM.getType());
        return new OssClient(null, properties);
    }

    public static String getS3FilePath(ArchiveResult archiveResult, ArchiveLocation archiveLocation) {
        String s3Url = String.format("http://%s/%s/archive/%d/%s.sql", archiveLocation.getS3Endpoint(),
            archiveLocation.getS3BucketName(), archiveResult.getTaskId(), archiveResult.getResultNo());
        if (OssConstant.IS_HTTPS.equals(archiveLocation.getS3IsHttps())) {
            s3Url = s3Url.replace("http", "https");
        }
        return s3Url;
    }

    public static void checkLocation(ArchiveLocation location) {
        if (location.getLocationType() == ArchiveLocationTypeEnum.DB.getValue()) {
            try (Connection connection = ArchiveUtil.getDbConnection(location)) {
                connection.isValid(2000);
            } catch (Exception e) {
                throw new ServiceException("数据库连接失败，请确认数据是否正确");
            }
        } else if (location.getLocationType() == ArchiveLocationTypeEnum.S3.getValue()) {
            try {
                OssClient client = ArchiveUtil.getS3Client(location);
                if (!client.doesBucketExistV2(location.getS3BucketName())) {
                    throw new ServiceException("对象存储" + location.getS3BucketName() + "不存在，请确认数据是否正确");
                }
            } catch (Exception e) {
                throw new ServiceException("对象存储连接失败，请确认数据是否正确");
            }
        }
    }

    public static String getCheckSql(String table, List<String> cells) {
        StringBuffer sb = new StringBuffer("SELECT ");
        for (String cell : cells) {
            sb.append("`").append(cell).append("`,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" FROM ").append(table).append(" LIMIT 1");
        return sb.toString();
    }
}
